package com.dcfcs.demo;
import org.apache.calcite.config.CalciteConnectionConfig;
import org.apache.calcite.config.CalciteConnectionProperty;
import org.apache.calcite.interpreter.BindableConvention;
import org.apache.calcite.interpreter.BindableRel;
import org.apache.calcite.interpreter.Bindables;
import org.apache.calcite.jdbc.CalciteSchema;
import org.apache.calcite.jdbc.JavaTypeFactoryImpl;
import org.apache.calcite.plan.RelOptCluster;
import org.apache.calcite.plan.RelOptPlanner;
import org.apache.calcite.plan.RelOptUtil;
import org.apache.calcite.prepare.CalciteCatalogReader;
import org.apache.calcite.rel.RelNode;
import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.rel.type.RelDataTypeFactory;
import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.fun.SqlStdOperatorTable;
import org.apache.calcite.sql.parser.SqlParseException;
import org.apache.calcite.sql.parser.SqlParser;
import org.apache.calcite.sql.type.SqlTypeName;
import org.apache.calcite.sql.validate.SqlValidator;
import org.apache.calcite.sql.validate.SqlValidatorUtil;
import org.apache.calcite.sql2rel.SqlToRelConverter;
import org.apache.calcite.sql2rel.StandardConvertletTable;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;
import com.dcfcs.foodmart.EndToEndExampleBindable.SchemaOnlyDataContext;
import static com.dcfcs.foodmart.EndToEndExampleBindable.NOOP_EXPANDER;
import static com.dcfcs.foodmart.EndToEndExampleBindable.newCluster;
import static  com.dcfcs.foodmart.EndToEndExampleBindable.*;

/**
 * 参考资料:
 *    https://www.bilibili.com/video/BV1uP4y1X7gU/?spm_id_from=333.337.search-card.all.click&vd_source=73c39493b7c7cb6da161e642b2eab6b9
 *    https://blog.csdn.net/qq_31183071/article/details/119722124
 *    https://cloud.tencent.com/developer/article/1936476?areaSource=102001.19&traceId=Nnof5iCDA2AKSm1iYJSQq
 *    https://zhuanlan.zhihu.com/p/655559608
 *    https://www.youtube.com/watch?v=p1O3E33FIs8&list=PLUp_iibbIwFF_5xeLRgz4zU33mFRbS5Y6&index=3
 */
public class TestCalcitePlan {

    private static final List<Object[]> AUTHOR_DATA = Arrays.asList(
             new Object[] {0, "Victor", "Hugo"},
             new Object[] {1, "Alexandre", "Dumas"}
    );

    private static final List<Object[]> BOOK_DATA = Arrays.asList(
            new Object[]{1, "Les Miserables", 1862, 0},
            new Object[]{2, "The Hunchback of Notre-Dame", 1829, 0},
            new Object[]{3, "The Last Day of a Condemned Man", 1829, 0},
            new Object[]{4, "The three Musketeers", 1844, 1},
            new Object[]{5, "The Count of Monte Cristo", 1884, 1}
    );


    public static void main(String[] args) throws SqlParseException {
        // Instantiate a. type factory for : creating types: (e.g. : VARCHAR, NUMERIC, etc.)
        //实例化A.类型工厂，用于：创建类型：(例如：VARCHAR、数值等)
         RelDataTypeFactory typeFactory = new JavaTypeFactoryImpl();

        // Create the root : schema: describing the data mode l
        // 创建根：schema：描述数据模式
        CalciteSchema schema = CalciteSchema.createRootSchema(false) ;
        // Define type for authors. table
        //定义作者的类型表
        RelDataType authorType = typeFactory.builder()
                                    .add("id", SqlTypeName.INTEGER)
                                    .add("fname", SqlTypeName.VARCHAR)
                                    .add("lname", SqlTypeName.VARCHAR)
                                     .build() ;
        //Initialize authors table with. data
        //初始化作者表和数据
        ListTable author = new ListTable(authorType, AUTHOR_DATA) ;

        // Add authors table to the schema
        // 将Authors表添加到架构中
        schema.add("author", author);
        schema.add("ar", author);

        // Define type for books. table
        //定义作者的类型表
        RelDataType bookType = typeFactory.builder()
                .add("id", SqlTypeName.INTEGER)
                .add("title", SqlTypeName.VARCHAR)
                .add("year", SqlTypeName.INTEGER)
                .add("author", SqlTypeName.INTEGER)
                .build() ;

               //. Initialize books table with data
               //。用数据初始化图书表
              ListTable bookTable = new ListTable(bookType, BOOK_DATA) ;
              // Add books table: to the: schema
              //：将图书表：添加到：schema
              schema.add("book", bookTable);
              // create an SQL parser
              SqlParser parser = SqlParser.create("SELECT title FROM book WHERE id > 1");
              //parse the query into an AST
              SqlNode parseNode = parser.parseQuery();
             //  Configure . and. instantiate . validator
             //  配置。和。实例化验证器
             CalciteCatalogReader catalogReader = new CalciteCatalogReader(schema, Collections.emptyList(),
                typeFactory, CalciteConnectionConfig.DEFAULT.set(CalciteConnectionProperty.CASE_SENSITIVE, "false"));
             SqlValidator validator = SqlValidatorUtil.newValidator(SqlStdOperatorTable.instance(),
                     catalogReader, typeFactory, SqlValidator.Config.DEFAULT);

             //Validate the initial AST
             SqlNode validNode = validator.validate(parseNode);
        /**
         * 验证后打印的结果为:
         *   SELECT `TITLE`
         *   FROM `BOOK`
         *   WHERE `BOOK`.`id` > 1
         */
        System.out.println("validNode打印的结果为:");
        System.out.println(validNode);
        System.out.println();

//             // Configure and instantiate the converter of the AST to Logical plan. (requires opt cluster
//             // Convert the valid AST into a logical plan
             RelOptCluster cluster = newCluster(typeFactory);
             SqlToRelConverter converter = new SqlToRelConverter(NOOP_EXPANDER, validator, catalogReader,
                     cluster, StandardConvertletTable.INSTANCE, SqlToRelConverter.config());
//             // Display the logical plan
             RelNode logicPlan = converter.convertQuery(validNode, false, true).rel ;
        /**
         * 逻辑执行计划
         * LogicalProject(TITLE=[$1])
         *   LogicalFilter(condition=[>($0, 1)])
         *     LogicalTableScan(table=[[book]])
         */
         System.out.println("逻辑执行计划为:");
         System.out.println(RelOptUtil.toString(logicPlan));
         System.out.println();
//             // Initialize optimizer/planner. with the necessary. rules
//             // 初始化优化器/计划器。带着必要的规则
             RelOptPlanner planner = cluster.getPlanner();
             planner.addRule(Bindables.BINDABLE_TABLE_SCAN_RULE);
             planner.addRule(Bindables.BINDABLE_FILTER_RULE);
             planner.addRule(Bindables.BINDABLE_PROJECT_RULE);
//             // Define the type: of the output plan: (in. this case we want a physical. pZan in BindableConvention)
//             //定义输出计划的类型：(in。这个案子我们要做个体检。PZan in BindableConvention”)
             logicPlan = planner.changeTraits(logicPlan,
                     cluster.traitSet().replace(BindableConvention.INSTANCE));
             planner.setRoot(logicPlan);
//
//             // Start the optimization process to obtain the most efficient physical plan based on the provided rule set.
//             //开始。这个。优化过程敬。获得最多。有效的物理计划：基于这个 提供规则准备好了。 RelNode -->  BindableRel
             BindableRel physicalPlan = (BindableRel) planner.findBestExp() ;
//
//             //Display the physicial plan
             System.out.println("优化后的逻辑执行计划为" );
             System.out.println(RelOptUtil.toString(physicalPlan));
             System.out.println("最后打印的结果为: ");
//
//            // Run the executable plan using a context simply providing access to the schema
//            //使用简单地提供对架构的访问的上下文运行可执行计划
            for( Object[] row : physicalPlan.bind(new SchemaOnlyDataContext(schema)).asEnumerable() ){

                System.out.println( Arrays.asList(row));
            }

    }
}
/**
 validNode打印的结果为:
 SELECT `TITLE`
 FROM `BOOK`
 WHERE `BOOK`.`id` > 1

 逻辑执行计划为:
 LogicalProject(TITLE=[$1])
 LogicalFilter(condition=[>($0, 1)])
 LogicalTableScan(table=[[book]])


 优化后的逻辑执行计划为
 BindableProject(TITLE=[$1])
 BindableFilter(condition=[>($0, 1)])
 BindableTableScan(table=[[book]])

 最后打印的结果为:
 [The Hunchback of Notre-Dame]
 [The Last Day of a Condemned Man]
 [The three Musketeers]
 [The Count of Monte Cristo]
 */